SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION [Live].[GetAssetByNAVDateOffset] (@RefNAVDate SMALLDATETIME, @DateOffset INT, @PortfolioId VARCHAR(10)
		, @InstrumentId INT, @LotId INT)
RETURNS TABLE
AS RETURN
(
	SELECT a.* FROM FSFundModel.Reporting.Asset a
	INNER JOIN  FSFundModel.Reporting.Portfolio p ON		
		p.ExportId = a.ExportId 
		AND   p.PortfolioId = a.PortfolioId
	CROSS APPLY	(
			SELECT TOP 1 OffsetExportId FROM Live.GetExportsByNAVDateOffset(@DateOffset, p.FundId) tvf
			WHERE tvf.BaseNAVDate < @RefNAVDate
			ORDER BY BaseNAVDate DESC 
	 ) ca
	WHERE a.ExportId = ca.OffsetExportId
	AND a.PortfolioId = @PortfolioId
	AND a.InstrumentId = @InstrumentId 
	AND a.LotId = @LotId 
	--ExportId = (SELECT MAX(ExportId) FROM FSFundModel.Reporting.Export WHERE NAVDate = @NAVDate AND FundId = @FundId)
)
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'Live', 'FUNCTION', N'GetAssetByNAVDateOffset', NULL, NULL
GO
